﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Web.Security;

/// <summary>
/// Summary description for EmployeeVacancy
/// </summary>
public class EmployeeMethord
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
    public EmployeeMethord()
	{
	}
    public void connClose()
    {
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
    }
    private void connOpen()
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
    }



    //METHORD CREATEVACANCY
    public SqlDataReader ReturnTypeOfJob()
    {
        SqlDataReader Sqldr = null;
        try
        {
            SqlCommand comm = new SqlCommand("Select * from SelectElementTypeJob", conn);
            connOpen();
            Sqldr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return Sqldr;
    }
    private string VacancyIDLast()
    {
        string VacancyID = "";
        try
        {
            SqlCommand comm = new SqlCommand("Select * from VacancyIDLast", conn);
            connOpen();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
            DataTable tb = ds.Tables[0];
            if (tb.Rows.Count > 0)
            {
                VacancyID = tb.Rows[tb.Rows.Count - 1]["VacancyID"].ToString();
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return VacancyID;
    }
    public string VacancyIDNew()
    {
        string VacancyID = VacancyIDLast();
        try
        {
            if (!VacancyID.Equals(""))
            {
                char[] cID = VacancyID.ToArray();
                int n = VacancyID.Count() - 1;
                VacancyID = "";
                for (int i = n; i >= 0; i--)
                {
                    try
                    {
                        int iu = Convert.ToInt32(cID[i].ToString());
                        if (iu < 9)
                        {
                            iu++;
                            VacancyID += iu;
                            for (int j = i - 1; j >= 0; j--)
                            {
                                VacancyID += cID[j].ToString();
                            }
                            break;
                        }
                        else
                        {
                            if (i == 1)
                            {
                                VacancyID += "01";
                            }
                            else
                            {
                                iu = 0;
                                VacancyID += iu;
                            }
                        }
                    }
                    catch (Exception)
                    {
                        VacancyID += cID[i].ToString();
                    }
                }
                char[] cUser = VacancyID.ToArray();
                VacancyID = "";
                for (int i = n; i >= 0; i--)
                {
                    VacancyID += cUser[i].ToString();
                }
            }
            else
            {
                VacancyID = "V0001";
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return VacancyID;
    }
    public bool InsertTableVacancy(string _Title, int _JobID, string _UserName, string _Description, int _NumberOfJob, string _Salary, string _Address, DateTime _DateCreatioin, DateTime _DateFulfilled)
    {

        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE InsertTableVacancyNew @VID, N'"+_Title+"', @JobID, @Username, N'"+_Description+"', @NumberOfJob, @NumberEmpty, @Salary, N'"+_Address+"', @DateCreate, @DateFulfilled, @Status", conn);
            comm.Parameters.AddWithValue("@VID", VacancyIDNew());
            comm.Parameters.AddWithValue("@JobID", _JobID);
            comm.Parameters.AddWithValue("@Username", _UserName);
            comm.Parameters.AddWithValue("@NumberOfJob", _NumberOfJob);
            comm.Parameters.AddWithValue("@NumberEmpty", _NumberOfJob);
            comm.Parameters.AddWithValue("@Salary", _Salary);
            comm.Parameters.AddWithValue("@DateCreate", _DateCreatioin);
            comm.Parameters.AddWithValue("@DateFulfilled", _DateFulfilled);
            comm.Parameters.AddWithValue("@Status", 1);
            connOpen();
            int check = comm.ExecuteNonQuery();
            if (check > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return result;
    }



    //METHORD CREATEINTERVIEW
    public SqlDataReader ReturnVacancyWithUserName(string _User)
    {
        SqlDataReader Sqldr = null;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnVacancyOfUserName @UserName", conn);
            comm.Parameters.AddWithValue("@UserName", _User);
            connOpen();
            Sqldr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return Sqldr;
    }

    public SqlDataReader ReturnApplicantWithVacancy(string _VacancyID)
    {
        SqlDataReader Sqldr = null;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnApplicantOfVacancy @VID", conn);
            comm.Parameters.AddWithValue("@VID", _VacancyID);
            connOpen();
            Sqldr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return Sqldr;
    }
    public bool CheckInterviewBusy(string _ID, string _Date, string _Time)
    {
        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInterviewerNotBusy @ID, @Date, @Time", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            comm.Parameters.AddWithValue("@Date", _Date);
            comm.Parameters.AddWithValue("@Time", _Time);
            connOpen();
            SqlDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return result;
    }
    public SqlDataReader ReturnInterviewer()
    {
        SqlDataReader Sqldr = null;
        try
        {
            SqlCommand comm = new SqlCommand("SELECT * FROM ReturnInterviewer", conn);
            connOpen();
            Sqldr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return Sqldr;
    }
    public bool InsertIntoTableInterview(string _InterviewerID, string _VacancyID, string _ApplicantID, DateTime _Date, string _Time)
    {
        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE InsertIntoTableInterview @InterviewerID, @VacancyID, @ApplicantID, @Date, @Time, 2", conn);
            comm.Parameters.AddWithValue("@InterviewerID", _InterviewerID);
            comm.Parameters.AddWithValue("@VacancyID", _VacancyID);
            comm.Parameters.AddWithValue("@ApplicantID", _ApplicantID);
            comm.Parameters.AddWithValue("@Date", _Date);
            comm.Parameters.AddWithValue("@Time", _Time);
            connOpen();
            int check = comm.ExecuteNonQuery();
            SqlCommand comm1 = new SqlCommand("EXECUTE UpdateStatusApplicant @ID, 5", conn);
            comm1.Parameters.AddWithValue("@ID", _ApplicantID);
            int check2 = comm1.ExecuteNonQuery();
            if (check > 0 && check2 > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return result;
    }
    public string ReturnNameEmployee(string _UserName)
    {
        string Name = "";
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnNameEmployee @User", conn);
            comm.Parameters.AddWithValue("@User", _UserName);
            connOpen();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
            DataTable tb = ds.Tables[0];
            if (tb.Rows.Count > 0)
            {
                Name = tb.Rows[0]["FullName"].ToString();
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return Name;
    }
    //MASTERPAGE
    public DataTable MenuTypeJob()
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("Select * from ReturnTypeJob", conn);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return tb;
    }
    public DataTable MenuVacancy(int _ID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnVacancyFollowTypeJob @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return tb;
    }

    public DataTable MenuVacancyEmployee(int _ID, string _User)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnVacancyFollowTypeAndUserName @ID, @User", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            comm.Parameters.AddWithValue("@User", _User);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return tb;
    }

    public DataTable MenuApplicant(int _ID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnVacancyFollowTypeJob @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return tb;
    }
    public SqlDataReader ReturnInterviewNotAppcess(string _User)
    {
        SqlDataReader dr = null;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInterviewNotAccept @ID", conn);
            comm.Parameters.AddWithValue("@ID", _User);
            connOpen();
            dr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
        }
        return dr;
    }



    //Page ChangeStatusApplicant
   
    public DataTable ReturnInformationApplicent(string _ApplicantID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInformationApplicant @ApplicantID", conn);
            comm.Parameters.AddWithValue("@ApplicantID", _ApplicantID);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return tb;
    }
    public SqlDataReader ReturnVacancyName(string _VacancyID)
    {
        SqlDataReader sqldr = null;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnTitleVacancyFollowVacancyID @VacancyID", conn);
            comm.Parameters.AddWithValue("@VacancyID", _VacancyID);
            connOpen();
            sqldr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return sqldr;
    }
    public bool UpdateStatusApplicant(string _ApplicantID, int _Status)
    {
        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE UpdateStatusApplicant @ApplicantID, @Status", conn);
            comm.Parameters.AddWithValue("@ApplicantID", _ApplicantID);
            comm.Parameters.AddWithValue("@Status", _Status);
            connOpen();
            int check = comm.ExecuteNonQuery();
            if (check > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return result;
    }
    public bool UpdateStatusVacancy(string _VacancyID, int _Status)
    {
        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE UpdateStatusVacancy @VacancyID, @Status", conn);
            comm.Parameters.AddWithValue("@VacancyID", _VacancyID);
            comm.Parameters.AddWithValue("@Status", _Status);
            connOpen();
            int check = comm.ExecuteNonQuery();
            if (check > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return result;
    }



    //METHORD PAGE CHANGEVACANCY
    public DataTable ReturnInformationVacancy(string _VacancyID, string _User)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInformationVacancy @ID, @User", conn);
            comm.Parameters.AddWithValue("@ID", _VacancyID);
            comm.Parameters.AddWithValue("@User", _User);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return tb;
    }
    public DataTable ReturnInformationVacancyInter(string _VacancyID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInformationVacancyInter @ID", conn);
            comm.Parameters.AddWithValue("@ID", _VacancyID);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return tb;
    }
    public bool UpdateVacancy(string _ID, string _Title, int _JobID, string _Description, int _NumberOfJob, string _Salary, string _WorkPlace, DateTime _DateFulfilled, int _Status)
    {
        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE UpdateVacancy @ID, @Title, @JobID, @Description, @NumberOfJob, @Salary, @WorkPlace, @DateFulfilled, @Status", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            comm.Parameters.AddWithValue("@Title", _Title);
            comm.Parameters.AddWithValue("@JobID", _JobID);
            comm.Parameters.AddWithValue("@Description", _Description);
            comm.Parameters.AddWithValue("@NumberOfJob", _NumberOfJob);
            comm.Parameters.AddWithValue("@Salary", _Salary);
            comm.Parameters.AddWithValue("@WorkPlace", _WorkPlace);
            comm.Parameters.AddWithValue("@DateFulfilled", _DateFulfilled);
            comm.Parameters.AddWithValue("@Status", _Status);
            connOpen();
            int check = comm.ExecuteNonQuery();
            if (check > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            connClose();
        }
        return result;
    }



    //METHORD ApplytoVacancy
    public DataSet ReturnApplicantApplytoVacancy(string _Vacancy)
    {
        DataSet ds = null;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnApplicantApplyVacancy @ID", conn);
            comm.Parameters.AddWithValue("@ID", _Vacancy);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            ds = new DataSet();
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return ds;
    }
    [System.Web.Services.WebMethod]
    public DataSet ReturnApplicantApplytoVacancyLike(string _Vacancy, string _Like, string _NameProcedure)
    {
        SqlCommand comm = null;
        DataSet ds = null;
        try
        {
            if (_NameProcedure.Equals("ID"))
            {
                comm = new SqlCommand("EXECUTE ReturnApplicantApplyVacancyLikeID @ID, @Like", conn);
            }
            else
            {
                comm = new SqlCommand("EXECUTE ReturnApplicantApplyVacancyLikeName @ID, @Like", conn);
            }
            comm.Parameters.AddWithValue("@ID", _Vacancy);
            comm.Parameters.AddWithValue("@Like", _Like);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            ds = new DataSet();
            da.Fill(ds);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return ds;
    }
    public DataSet ReturnApplicantApplytoVacancySatisfactory(string _ID)
    {
        DataSet ds = new DataSet();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnApplicantApplyToVacancySatisfactory @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return ds;
    }


    public DataTable ApplicantDetails(string _AppId) {
        DataTable tb = new DataTable();
        try
        {
            if(conn.State == ConnectionState.Closed){
                conn.Open();
            }
            SqlCommand cmm = new SqlCommand("EXECUTE viewApplicant @ApplicantID", conn);
            cmm.Parameters.AddWithValue("@ApplicantID",_AppId);
            SqlDataAdapter sqlda = new SqlDataAdapter(cmm);
            DataSet ds = new DataSet();
            sqlda.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {

            Console.Write(ex.Message);
        }
        return tb;
    }
    public SqlDataReader loaddatadropdownlist() {
        SqlDataReader sqldr = null;
        try
        {
            if(conn.State == ConnectionState.Closed){

                conn.Open();
            }
            SqlCommand com = new SqlCommand("SELECT * FROM vw_allVacancyID", conn);
            sqldr = com.ExecuteReader();
        }
        catch (Exception ex)
        {

            Console.Write(ex.Message);
        }
        return sqldr;

    }
    public bool Attachapplicant(string _AppID, string _VacancyID) {
        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE AttachApplicant @ApplicantID,@VacancyID", conn);
            comm.Parameters.AddWithValue("@ApplicantID",_AppID);
            comm.Parameters.AddWithValue("@VacancyID",_VacancyID);
                    
            
            connOpen();
            int check = comm.ExecuteNonQuery();
            if (check > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            connClose();
        }
        return result;
    }
    



    //PAGE APPLICANT INFORMATION
    public DataTable ReturnApplicantIDInformationWithID(string _ID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnApplicanInformationWithID @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return tb;
    }
    public string ReturnTitleVacancyWithID(string _ID)
    {
        string Title = "";
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnTitleVacancyFollowVacancyID @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataReader da = comm.ExecuteReader();
            while(da.Read())
            {
                Title = da.GetString(1);
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return Title;
    }



    //METHORD PAGE HRViewVacancyDatails
    public string ReturnNameJobType(int _ID)
    {
        string JobName = "";
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnNameJobType @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                JobName = dr.GetString(0);
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return JobName;
    }



    //METHORD PAGE HRViewInterviewerInfor
    public SqlDataReader ReturnInterviewerInforFollowID(string _ID)
    {
        SqlDataReader dr = null;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInterviewerInforFollowID @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            dr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
        }
        return dr;
    }



    //METHORD PAGE HRViewInerterInformation
    public DataSet ReturnAllInterviewInfor(string _User)
    {
        DataSet ds = new DataSet();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnAllIterviewInformation @User", conn);
            comm.Parameters.AddWithValue("@User", _User);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return ds;
    }
    public string ReturnNameFollowID(string _ID, string _Table)
    {
        string FullName = "";
        try
        {
            SqlCommand comm = null;
            if (_Table.Equals("Intervewer"))
            {
                comm = new SqlCommand("EXECUTE ReturnNameInterviewer @ID", conn);
            }
            else
            {
                comm = new SqlCommand("EXECUTE ReturnNameApplicant @ID", conn);
            }
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                FullName = dr.GetString(0);
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return FullName;
    }
    public DataSet ReturnDataSearch(string _TextSearch, string _TypeSearch, string _User)
    {
        DataSet ds = new DataSet();
        try
        {
            SqlCommand comm = null;
            switch (_TypeSearch)
            {
                case "InterviewID":
                    comm = new SqlCommand("EXECUTE HRSearchInterviewID @ID, @TextSearch", conn);
                    comm.Parameters.AddWithValue("@ID", _User);
                    comm.Parameters.AddWithValue("@TextSearch", _TextSearch);
                    break;
                case "Interviewer Name":
                    comm = new SqlCommand("EXECUTE HRSearchInterviewerName @ID, N'"+_TextSearch+"'", conn);
                    comm.Parameters.AddWithValue("@ID", _User);
                    break;
                case "VacancyID":
                    comm = new SqlCommand("EXECUTE HRSearchVacancyID @ID, @TextSearch", conn);
                    comm.Parameters.AddWithValue("@ID", _User);
                    comm.Parameters.AddWithValue("@TextSearch", _TextSearch);
                    break;
                case "ApplicantID":
                    comm = new SqlCommand("EXECUTE HRSearchApplicantID @ID, @TextSearch", conn);
                    comm.Parameters.AddWithValue("@ID", _User);
                    comm.Parameters.AddWithValue("@TextSearch", _TextSearch);
                    break;
                case "Applicant Name":
                    comm = new SqlCommand("EXECUTE HRSearchApplicantName @ID, N'"+_TextSearch+"'", conn);
                    comm.Parameters.AddWithValue("@ID", _User);
                    break;
                case "Date Interview":
                    int Year = Convert.ToDateTime(_TextSearch).Year;
                    int Month = Convert.ToDateTime(_TextSearch).Month;
                    int Day = Convert.ToDateTime(_TextSearch).Day;
                    string date = ""+Year;
                    if (Month < 10)
                        date += "-0" + Month;
                    else
                        date += "-" + Month;
                    if (Day < 10)
                        date += "-0" + Day;
                    else
                        date += "-" + Day;
                    comm = new SqlCommand("EXECUTE HRSearchDateInterview @ID, @TextSearch", conn);
                    comm.Parameters.AddWithValue("@ID", _User);
                    comm.Parameters.AddWithValue("@TextSearch", date);
                    break;
                case "Status":
                    comm = new SqlCommand("EXECUTE HRSearchStatus @ID, @TextSearch", conn);
                    comm.Parameters.AddWithValue("@ID", _User);
                    comm.Parameters.AddWithValue("@TextSearch", _TextSearch);
                    break;
            }
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return ds;
    }


    //Page VacancyManager
    public DataSet ReturnAllVacancyInformation(string _User)
    {
        DataSet ds = new DataSet();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnAllVacancyInformationFollowUser @User", conn);
            comm.Parameters.AddWithValue("User", _User);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return ds;
    }
    public DataSet ReturnVacancyInforFollowSearch(string _User, string _TypeSearch, string _TextSearch)
    {
        DataSet ds = new DataSet();
        try
        {
            SqlCommand comm = null;
            if (_TypeSearch.Equals("VacancyID"))
            {
                comm = new SqlCommand("EXECUTE ReturnVacancyInforFollowSearchID @User, @ID", conn);
            }
            else
            {
                comm = new SqlCommand("EXECUTE ReturnVacancyInforFollowSearchName @User, @ID", conn);
            }
            comm.Parameters.AddWithValue("@User", _User);
            comm.Parameters.AddWithValue("@ID", _TextSearch);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return ds;
    }
    public DataTable SearchbyName(string _InterviewerName)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcom = new SqlCommand("SELECT * FROM vw_SearchbyName where FullName like N'%" + _InterviewerName + "%'", conn);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(sqlcom);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return tb;
    }
    public DataTable SearchByID(string _ID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcom = new SqlCommand("SELECT * FROM vw_SearchByID WHERE InterviewerID  like N'%" + _ID + "%'", conn);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(sqlcom);

            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {

            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return tb;
    }
    public DataSet viewAllVacancy()
    {
        DataSet ds = new DataSet();
        try
        {
            SqlCommand sqlcomm = new SqlCommand("SELECT * FROM viewallVacancy", conn);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(sqlcomm);
            da.Fill(ds);
        }
        catch (Exception ex)
        {


        }
        return ds;
    }
    public DataTable SearchByDate(string _newDate)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcomm = new SqlCommand("EXECUTE SearchByDate @Datefulfilled", conn);
            sqlcomm.Parameters.AddWithValue("@Datefulfilled", Convert.ToDateTime(_newDate));
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(sqlcomm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            connClose();
        }
        return tb;
    }
    public DataTable viewallinterviewer()
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcom = new SqlCommand("SELECT * FROM vw_AllInterviewer", conn);
            SqlDataAdapter da = new SqlDataAdapter(sqlcom);
            connOpen();
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            conn.Close();
        }
        return tb;
    }
    public DataTable AdminSearchHR() {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcm = new SqlCommand("SELECT * FROM vw_AdminSearchHR", conn);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(sqlcm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {

        }
        return tb;
    }


    //Page LoginMaster
    public bool CheckLogin(string _User, string _Pass, string _Type)
    {
        bool Result = false;
        try
        {
            string Pass = FormsAuthentication.HashPasswordForStoringInConfigFile(_Pass, "SHA1");
            SqlCommand comm = null;
            switch (_Type)
            {
                case "Interviewer":
                    comm = new SqlCommand("EXECUTE CheckLoginInterviewer @ID, @Pass", conn);
                    break;
                case "HR":
                    comm = new SqlCommand("EXECUTE CheckLoginEmployee @ID, @Pass", conn);
                    break;
                default:
                    comm = new SqlCommand("EXECUTE CheckLoginAdmin @ID, @Pass", conn);
                    break;
            }
            comm.Parameters.AddWithValue("@ID", _User);
            comm.Parameters.AddWithValue("@Pass", Pass);
            connOpen();
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                Result = true;
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return Result;
    }
    public int UpdatePassword(string _User, string _Pass, string _Type)
    {
        int Result = 0;
        try
        {
            string Pass = FormsAuthentication.HashPasswordForStoringInConfigFile(_Pass, "SHA1");
            SqlCommand comm = null;
            switch (_Type)
            {
                case "Interviewer":
                    comm = new SqlCommand("EXECUTE UpdatePasswordInterviewer @ID, @Pass", conn);
                    break;
                default:
                    comm = new SqlCommand("EXECUTE UpdatePasswordEmployee @ID, @Pass", conn);
                    break;
            }
            comm.Parameters.AddWithValue("@ID", _User);
            comm.Parameters.AddWithValue("@Pass", Pass);
            connOpen();
            Result = comm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return Result;
    }
    //Emoloyee Information
    public DataTable informationInterviewer(string _interviewerID) {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcom = new SqlCommand("EXECUTE InterviewerInformation @Interviewer", conn);
            sqlcom.Parameters.AddWithValue("@Interviewer", _interviewerID);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(sqlcom);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
        }
        finally 
        {
            connClose();
        }
        return tb;
    }
    public bool UpdateInterviewer(string _Fulname, string _Address, string _phone, DateTime _birth, string _Email, string _username)
    {
        bool resurt = false;
        try
        {
            SqlCommand sqlcom = new SqlCommand("EXECUTE UpdateInterviewer @FullName, @Address, @Phone, @BirthOfDate, @EmailID, @UserName", conn);

            sqlcom.Parameters.AddWithValue("@FullName", _Fulname);
            sqlcom.Parameters.AddWithValue("@Address", _Address);
            sqlcom.Parameters.AddWithValue("@Phone", _phone);
            sqlcom.Parameters.AddWithValue("@BirthOfDate", _birth);
            sqlcom.Parameters.AddWithValue("@EmailID", _Email);
            sqlcom.Parameters.AddWithValue("@UserName", _username);
            connOpen();
            int check = sqlcom.ExecuteNonQuery();
            if (check > 0)
            {
                resurt = true;
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return resurt;
    }
    //Hr information
    public DataTable HrInformation(string _Username) {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcom = new SqlCommand("EXECUTE HrInformation @UserName",conn);
            sqlcom.Parameters.AddWithValue("@UserName",_Username);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(sqlcom);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
        }
        finally {
            connClose();
        }
        return tb;
    }
    public bool UpdateInformationHR(string _Fulname, string _Address, string _phone, DateTime _birth, string _Email, string _username)
    {
        bool resurt = false;
        try
        {
            SqlCommand sqlcom = new SqlCommand("EXECUTE UpdateInformationHR @FullName,@Address,@Phone,@BirthOfDate,@EmailID , @UserName", conn);

            sqlcom.Parameters.AddWithValue("@FullName", _Fulname);
            sqlcom.Parameters.AddWithValue("@Address", _Address);
            sqlcom.Parameters.AddWithValue("@Phone", _phone);
            sqlcom.Parameters.AddWithValue("@BirthOfDate", _birth);
            sqlcom.Parameters.AddWithValue("@EmailID", _Email);
            sqlcom.Parameters.AddWithValue("@UserName", _username);
            connOpen();
            int check = sqlcom.ExecuteNonQuery();
            if (check > 0)
            {
                resurt = true;
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return resurt;
    }
}